package zuoye.Demo2.dao;
import Demo2.entity.ExamResult;
import Demo2.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ExamResultDao implements IexamResult {
    @Override
    public List<ExamResult> exaAll() {
        List<ExamResult> list =new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        conn = DBUtil.getConnection();
        String sql ="select * from s_examresult";
        try {
            ps =conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                ExamResult e =new ExamResult();
                e.setId(rs.getLong("id"));
                e.setExamDate(rs.getDate("exam_date"));
                e.setScore(rs.getFloat("score"));
                e.setStId(rs.getLong("st_id"));
                e.setSbId(rs.getLong("sb_id"));
                list.add(e);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }
        return list;
    }

    @Override
    public int exaAdd(ExamResult e) {
        Connection conn =null;
        PreparedStatement ps =null;
        conn =DBUtil.getConnection();
        String sql ="INSERT INTO `s_examresult` (`exam_date`, `score`, `st_id`, `sb_id`) VALUES (?, ?, ?, ?)";
        try {
            ps =conn.prepareStatement(sql);
            ps.setDate(1, (Date) e.getExamDate());
            ps.setFloat(2,e.getScore());
            ps.setLong(3,e.getStId());
            ps.setLong(4,e.getSbId());
            int num = ps.executeUpdate();
            return num;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            DBUtil.close(ps,conn);
        }
        return 0;
    }

    @Override
    public int exaUpd(ExamResult e) {
        Connection conn =null;
        PreparedStatement ps =null;
        conn =DBUtil.getConnection();
        String sql ="UPDATE `s_examresult` SET `exam_date` = ?, `score` = ?, `st_id` = ?, `sb_id` = ? WHERE `id` = ?";
        try {
            ps =conn.prepareStatement(sql);
            ps.setDate(1, (Date) e.getExamDate());
            ps.setFloat(2,e.getScore());
            ps.setLong(3,e.getStId());
            ps.setLong(4,e.getSbId());
            ps.setLong(5,e.getId());
            int num = ps.executeUpdate();
            return num;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }finally {
            DBUtil.close(ps,conn);
        }
        return 0;
    }

    @Override
    public int exaDel(long id) {
        Connection conn =null;
        PreparedStatement ps =null;
        conn =DBUtil.getConnection();
        String sql ="delete from s_examresult where id=?";
        try {
            ps =conn.prepareStatement(sql);
            ps.setLong(1,id);
            int num = ps.executeUpdate();
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps,conn);
        }
        return 0;
    }
}
